<?php
date_default_timezone_set('PRC');
session_start();
if ($_SESSION["islogin"] !== 'igiveyouthepower') {
    echo "<script language=JavaScript>\r\n";
    echo "alert('请先登录');\r\n";
    echo "location.href='../index.php'\r\n";
    echo "</script>";
}
require_once('../config.php');
$conn = mysqli_connect($cfg_dbhost,$cfg_dbuser,$cfg_dbpwd,$cfg_dbname);

$lang = $_GET["lang"];
$bname = $_GET["bname"];

$operate = $_POST['operate'];
$size = $_POST['size'];
$brand = $_POST['brand'];
$classify = $_POST['classify'];
$chinese = $_POST['chinese'];
$english = $_POST['english'];
$carriage = $_POST['carriage'];
$registration = $_POST['registration'];
$roughWeight = $_POST['roughWeight'];
$packSize = $_POST['packSize'];
$materials = $_POST['materials'];
$packMaterials = $_POST['packMaterials'];
$metal = $_POST['metal'];
$jewelry = $_POST['jewelry'];


if ($_SESSION["thetype"] == "管理员" || $_SESSION["thetype"] == "超级管理员") {
    $sql = "select * from ph_" . $lang . " where 1=1";
} else {
    $sql = "select * from ph_" . $lang . " where 1=1 and user_name='" . $_SESSION["managername"] . "'";
}
if ($bname !== '' && isset($bname)) {
    $sql .= " and bname ='" . $bname . "'";
}
$result = mysqli_query($conn,$sql);

require '../PHPExcel/PHPExcel.php';
require '../PHPExcel/PHPExcel/Writer/Excel2007.php';

//创建一个excel
$objPHPExcel = new PHPExcel();

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

//设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
//设置sheet的name,excel文件名
$fileName = $bname . "-" . date('Ymdhis', time());

$headinfo = array(
    '父SKU',
    'SKU',
    '操作',
    '颜色',
    '尺码',
    '品牌',
    '分类',
    '中文简称',
    '英文简称',
    '库存',
    '币种',
    '成本价',
    '运费',
    '挂号模板',
    '分销价',
    '毛重',
    '包装尺寸',
    '适用人群',
    '材料',
    '包装材料',
    '金属',
    '珠宝',
    '语言',
    '标题',
    '关键字',
    '要点1',
    '要点2',
    '要点3',
    '要点4',
    '要点5',
    '简介',
    '产品图',
    '参考网址'
);

//设置指定单元格宽度
for ($i = 0; $i < count($headinfo); $i++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension(IntToChr($i))->setWidth(30);
}

//水平居中
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置起始位置生成表格
$startX = 'A';
$startY = '1';
for ($i = 0; $i < count($headinfo); $i++) {
    //水平居中
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //垂直居中
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getActiveSheet()->setCellValue($startX++ . $startY, $headinfo[$i]);
    //设置单元格属性(文本属性)
    $objPHPExcel->getActiveSheet()->getStyle($startX . $startY)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
}

$mun = 1;
while ($row = mysqli_fetch_array($result)) {
    if (is_array($row) && count($row) > 0) {
	$f=0;
        if (!empty($row['sketch'])){$maidian=$row['sketch'];}else{$maidian=$row['des'];}
		$spliter = "<br/>";
		if(strpos($maidian,$spliter)==0){$f=1;}
        if (strpos($maidian, $spliter) !== false) {
        $des = explode($spliter, $maidian);
    } else {
        $des = explode("\r\n", $maidian);
    }
    $des1 = $des[$f];
    $des2 = $des[$f+1];
    $des3 = $des[$f+2];
    $des4 = $des[$f+3];
    $des5 = $des[$f+4];
        //}

        $sqls = "select * from ph_shuxing where proid='" . $row['ID'] . "' order by ID asc";
        $results = mysqli_query($conn,$sqls);
//print_r($row);die;
        //主图（
        $sqla = "select * from ph_img where proid='" . $row['ID'] . "' and btid is null order by px asc ";
        $resulta = mysqli_query($conn,$sqla);
        $imga = '';
        while ($rowa = mysqli_fetch_array($resulta)) {
            if (!empty($rowa['imgpath'])) {
                $imga .= "http://" . $_SERVER['SERVER_NAME'] . "/products/" . $rowa['imgpath'] . "|";
            }
        }
        if(mysqli_num_rows($results)<1){
		$mun++;
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $mun, $row['skucode']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $mun, '');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $mun, $operate);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $mun, '');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $mun, $size);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $mun, $brand);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $mun, $classify);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $mun, $chinese);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $mun, $english);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $mun, $row['kucun']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $mun, $row['coin']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $mun, '');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $mun, $carriage);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . $mun, $registration);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . $mun, $row['saleprice']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $mun, $roughWeight);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $mun, $packSize);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $mun, '全部');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . $mun, $materials);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $mun, $packMaterials);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . $mun, $metal);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . $mun, $jewelry);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $mun, '英语');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $mun, $row['proname']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $mun, $row['keywords']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . $mun, $des1);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . $mun, $des2);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $mun, $des3);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $mun, $des4);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $mun, $des5);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $mun, $row['des']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $mun, $imga);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AG' . $mun, $row['fromurl']);
		}
    }
    while ($rows = mysqli_fetch_array($results)) {
        $mun++;
        if (strpos($rows['cname'], ",") !== false) {
            $cname = explode(",", $rows['cname']);
            $cname1 = $cname[0];
            $cname2 = $cname[1];
        } else {
            $cname1 = $rows['cname'];
            $cname2 = "";
        }

        $sqlb = "select * from ph_img where proid='" . $row['ID'] . "' and btid='" . $rows['ID'] . "' order by px asc";
        $resultb = mysqli_query($conn,$sqlb);
        $imgb = '';
        while ($rowb = mysqli_fetch_array($resultb)) {
            if (!empty($rowb['imgpath'])) {
                $imgb .= "http://" . $_SERVER['SERVER_NAME'] . "/products/" . $rowb['imgpath'] . "|";
            }
        }

        $sku = empty($rows['skucode']) ? randomsku(19) : $rows['skucode'];
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $mun, $row['skucode']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $mun, $sku);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $mun, $operate);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $mun, $cname1);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $mun, $cname2);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $mun, $brand);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $mun, $classify);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $mun, $chinese);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $mun, $english);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $mun, $row['kucun']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $mun, $row['coin']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('L' . $mun, $row['saleprice']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('M' . $mun, $carriage);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('N' . $mun, $registration);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('O' . $mun, $row['saleprice']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('P' . $mun, $roughWeight);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Q' . $mun, $packSize);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('R' . $mun, '全部');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('S' . $mun, $materials);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('T' . $mun, $packMaterials);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('U' . $mun, $metal);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('V' . $mun, $jewelry);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('W' . $mun, '英语');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('X' . $mun, $row['proname']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Y' . $mun, $row['keywords']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('Z' . $mun, $des1);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AA' . $mun, $des2);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AB' . $mun, $des3);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AC' . $mun, $des4);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AD' . $mun, $des5);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AE' . $mun, $row['des']);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AF' . $mun, $imgb.$imga);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('AG' . $mun, $row['fromurl']);
    }
}
/*
 * Job:导出无数据空白页面问题修改
 * 下面代码从IF中拿到外面
 */
ob_end_clean();
header("Pragma: public");
header("Expires: 0");
header('Cache-Control: max-age=0'); //禁止缓存
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename=' . $fileName . '.xls');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');


/**
 * 数字转字母 （类似于Excel列标）
 * @param Int $index 索引值
 * @param Int $start 字母起始值
 * @return String 返回字母
 */
function IntToChr($index, $start = 65)
{
    $str = '';
    if (floor($index / 26) > 0) {
        $str .= IntToChr(floor($index / 26) - 1);
    }
    return $str . chr($index % 26 + $start);
}

/**
 * 处理字符串中多个连续br替换一个
 */
function handContent($str = '')
{
    $arr = array_filter(explode("<br/>", $str));
    return implode("<br/>", $arr);
}

function randomsku($length)
{
    $key = '';
    $pattern = '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLOMNOPQRSTUVWXYZ';
    for ($i = 0; $i < $length; $i++) {
        $key .= $pattern{mt_rand(0, 35)};    //生成php随机数
    }
    return $key;
}